package com.alibaba.zihao.service;

import java.sql.*;

/**
 * Created by jason on 3/9/15.
 */
public class JDBCConnect {

    private Connection cnn = null;

    private PreparedStatement preparedStatement = null;

    private ResultSet queryResult = null;

    private int updateResult ;

//    String url = "jdbc:mysql://localhost:3066/dzh_mysql";
//    String username = "zihao" ;
//    String password = "haozi" ;

    public JDBCConnect(){
        cnn = ConnectMysql();
    }

    private Connection ConnectMysql() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/dzh_mysql?user=zihao&password=haozi";
            cnn = DriverManager.getConnection(url);
        } catch(Exception e){
            System.out.println("Connect fail!");
            e.printStackTrace();
        }
        finally {
            return cnn;
        }
    }

    public ResultSet query(String sql){

        try{
            preparedStatement = cnn.prepareStatement(sql);
            queryResult = preparedStatement.executeQuery();
        }catch (SQLException e){
            System.out.println("query fail");
            e.printStackTrace();
        }finally {
            return queryResult;
        }
    }

    public int update(String sql){

        try{
            preparedStatement = cnn.prepareStatement(sql);
            updateResult = preparedStatement.executeUpdate();
        }catch (SQLException e){
            System.out.println("update fail");
            e.printStackTrace();
        }finally {
            return updateResult;
        }
    }

    public boolean coles(){

        boolean isColes = false;
        if(queryResult!=null){
            try {
                queryResult.close();
                queryResult=null;
                isColes=true;
            } catch (SQLException e) {
                isColes=false;
                e.printStackTrace();
                System.out.println("关闭结果集发生错误");
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
                preparedStatement=null;
                isColes=true;
            } catch (SQLException e) {
                isColes=false;
                e.printStackTrace();
                System.out.println("关闭pstmt发生异常");
            }
        }
        if(cnn!=null){
            try{
                cnn.close();
                cnn=null;
                isColes=true;
            }catch (Exception e) {
                isColes=false;
                e.printStackTrace();
                System.out.println("关闭conn发生异常");
            }
        }
        return isColes;
    }

    public void testQuery() throws SQLException{
        queryResult = query("select * from stdScore");
        System.out.println(queryResult);
        while(queryResult.next()){
            System.out.print(queryResult.getString(1));
            System.out.print(queryResult.getString(2));
            System.out.print(queryResult.getString(3));
            System.out.println(queryResult.getString(4));

        }
    }

}